<?
/**
* @copyright (C) 2005 - 2008 SystN International Pty Ltd.
* @license http://www.systn.com
* SystN is Free Software
*/

//驱动处理
class DBSQL{
	/**
	*  处理数据库载入单元
	*/
	function DBSQL($DBA=''){
		
		//debug 文件目录
		$this->debug_file = $DBA['sqllog'];
		
		
		//当前数据库类型
		$this->dbtype = $DBA['dbtype'] = strtolower($DBA['dbtype']);
		
		//服务器检测
		if(eregi('mysql',$DBA['dbtype'])){
			$this->db_name  = 'MYSQL';
			$func_name   = $DBA['dbtype'].'_connect';
		}elseif(eregi('mssql',$DBA['dbtype'])) {
			$this->db_name  = 'SQL Server';
			$func_name = $DBA['dbtype'].'_connect';
		}elseif(eregi('sqlite',$DBA['dbtype'])) {
			$this->db_name  = 'SQLite';
			$func_name = $DBA['dbtype'].'_open';
		}elseif(eregi('pgsql',$DBA['dbtype'])) {
			$this->db_name  = 'PostgreSQL';
			$func_name = 'pg_connect';
		}elseif(eregi('odbc',$DBA['dbtype'])) {
			$this->db_name  = 'ODBC';
			$func_name = $DBA['dbtype'].'_pconnect';
		}else{
			$die_code  = 'Sorry, Drive Link does not support this kind of database. ';
			$this->log_write($die_code,'red');
			die($die_code);
		}
		
		
		if (!function_exists($func_name)){
			$die_code  = 'Your server does not support '.$DBA['dbtype'].' database.';
			$this->log_write($die_code,'red');
			die($die_code);
		}
		
		
		//引入驱动
		if(!is_file(dirname(__FILE__)."/".$DBA['dbtype'].".php")){
			$die_code  = 'Fail to load database '.$DBA['dbtype'];
			$this->log_write($die_code,'red');
			die($die_code);
		}
		include_once dirname(__FILE__)."/".$DBA['dbtype'].".php";
		//声明驱动单元
		$this -> DB = new Dirver;
		$error_id   = $this -> DB -> DBLink($DBA);
		
		//connect error
		$dbname = (trim($DBA['dbname'])=='')?'':' <b>DB:</b>'.$DBA['dbname'];
		$dbuser = (trim($DBA['dbuser'])=='')?'':' <b>USER:</b>'.$DBA['dbuser'];
		if ($error_id==1){
			$die_code  = 'Fail to connect '.$this->db_name.' (<b>SERVER:</b>'.$DBA['dbhost'].$dbuser.$dbname.'). <br>Please check the configuration information.';
			$this->log_write($die_code,'red');
			die($die_code);
		}else{
			//连接成功写入记录
			$die_code  = 'Succeed to connect '.$this->db_name.' (<b>SERVER:</b>'.$DBA['dbhost'].$dbuser.$dbname.').';
			$this->log_write($die_code,'#008000');
		}
		
		//db error
		if ($error_id==2){
			$die_code  = 'Cannot use database '.$DBA['dbname'];
			$this->log_write($die_code,'red');
			die($die_code);
		}
		
	}
	
	
	/**
	*  返回数组资料行
	*/
	function rows($query) {
		return $this->fetch_array($this->query($query));
	}
	
	
	/**
	*  返回数组行
	*/
	function nums($query) {
		return $this->num_rows($this->query($query));
	}
	
	
	/**
	*  一个查询字符串
	*  limit大于0则表示限制数据集，小于0则禁止错误调试
	*/
	function query($sql,$limit=''){
		$limit = (int)$limit;
		
		
		$query = $this -> DB -> query($sql,$limit);
		
		//连接关闭提示
		if (!$query && !eregi('INSERT',$sql) && !mysql_error()){
			$die_code = $this->color($sql).'<br>'.$this->db_name.' database has been closed and unable to implement query operation.';
			$this->error($sql,5179,$die_code);
		}
		
		
		$this->log_write($this->color($sql));
		
		if(!$query){
			$this->error($sql);
		}
		return $query;
	}
	
	
	/**
	*	插入数据表记录行
	*	table	表名
	*	data	插入数据的数组array('username'=>'md-chinese')
	*/
	function insert($table, $data){
		$sql_field = implode('`, `', array_keys($data));
		$sql_value = implode("', '",$data);
		if (is_array($data)){
			return $this->query('INSERT INTO `'.$table .'` (`'.$sql_field.'`) VALUES (\''.$sql_value.'\')');
        }
    }
    
    
	/**
	*	更新数据表记录行
	*	table	表名
	*	data	插入数据的数组array('username'=>'md-chinese')
	*	table	条件语句
	*/
    function update($table, $data, $parameters){
        reset($data);
		foreach (array_keys($data) AS $k=>$v) {
			if (eregi('add::([0-9]+)',$data[$v],$args)){
				$attribute[] = "`$v` = $v+".(int)$args[1];
			}else{
				$attribute[] = "`$v` = '".$data[$v]."'";
			}
		}
		
		//条件属性
		$parameters = ($parameters)?' WHERE '.$parameters:'';
		
		if (is_array($attribute)){
        	return $this->query('UPDATE `'.$table.'` SET '.implode(",",$attribute).$parameters);
		}
    }
	
	
	/**
	*  取得返回列的数目
	*/
	function num_rows($query) {
		return $this -> DB -> num_rows($query);
	}
	
	
	/**
	*  返回数组资料
	*/
	function fetch_array($query){
		return $this -> DB -> fetch_array($query);
	}
	
	
	/**
	*  返回数组资料
	*/
	function list_array($info){
		if($info['query'] && trim($info['seek'])){
			mssql_data_seek($info['query'],$info['seek']);
			return $this -> DB -> fetch_array($info['query']);
		}else{
			return $this -> DB -> fetch_array($info['query']);
		}
	}
	
	
	/**
	*  返回最后一次插入指令的 ID
	*/
	function insert_id(){
		$insertid = $this -> DB -> insert_id();
		$this->log_write('Get '.$this->db_name.' and  insert ID. (Value:'.$insertid.')','#000080');
		return $insertid;
	}
	
	
	/**
	*  关闭当前数据库连接
	*/
	function close(){
		
		$this->log_write($this->db_name.' connection closed.','#008000');
		return $this -> DB -> close();
	}
	
	
	/**
	*  检测数据库版本
	*/
	function version(){
		$ver = $this -> DB -> version();
		$this->log_write('Get '.$this->db_name.' version number. (Value:'.$ver.')','#000080');
		return $ver;
	}
	
	
	/**
	*  分页预处理函数
	*  sql		SQL语句
	*  page		当前页数
	*  limit	每页显示的数量
	*  maxs		查询总数
	*/
	function limit($sql,$page='0',$limit=10,$maxs=''){
		$show = Array();
		$page = (trim($page)>0)?$page:1;
		$limit = (trim($limit)>0)?$limit:1;
		
		$this->log_write('Page by page query start!','#000080');
		
		//信息数
		if(trim($maxs)==''){
			$order	= preg_replace("/.+(order.+)/i","\\1",$sql);
			if(eregi('order',$order)){
				$sqls	= str_replace($order,"",$sql);
			}else{
				$order	= '';
				$sqls	= $sql;
			}
			
			//对关联语句的判断
			if (!eregi("join",$sql)){
				$counts = $this -> fetch_array($this -> query( preg_replace("/order by.+?sc/is","",preg_replace("/SELECT.+ FROM /is","SELECT COUNT(*) AS cnt FROM ",$sql)) ));
				$Alls	 = $counts['cnt'];
			}else{
				$Alls = $this -> num_rows($this -> query( $sql ));
			}
			
		}else{
			$Alls = $maxs;
		}
		
		
		$MaxPages	= (int)ceil($Alls/$limit);							//处理分了几页
		$QuerySeek	= (($page > $MaxPages)? $MaxPages: $page-1)*$limit;	//数据查询指针数
		$QuerySeek  = ($QuerySeek<=0)?0:$QuerySeek;
		
		
		switch($this->dbtype){
			case'mssql':
				
				//ID
				$id  = @mssql_fetch_array(mssql_query("SELECT name FROM syscolumns WHERE id=object_id(N'users') AND COLUMNPROPERTY(id,name,'IsIdentity')=1"));
				
				$sql = preg_replace('/^SELECT\s/i', 'SELECT TOP '.$limit.' ', $sqls);
				//判断where 
				if(eregi('WHERE',$sql)){
					$sql	= preg_replace("/WHERE/i","WHERE",$sql);
					$sql	= preg_replace('/FROM\s([a-z0-9_]+)/i', "FROM \\1 [W] ".$id['name']." NOT IN (SELECT TOP ".$QuerySeek." ".$id['name']." FROM \\1 order by ".$id['name'].") [W]", $sql);
					$wtop	= preg_replace('/.+\[W\](.+)\[W\].+/i', "\\1", $sql);
					$sql	= str_replace('[W]'.$wtop.'[W]', "",$sql);
					//还原数据
					$sql	= str_replace('WHERE', "WHERE $wtop AND",$sql);
					
				}else{
					$sql = preg_replace('/FROM\s([a-z0-9_]+)/i', "FROM \\1 WHERE ".$id['name']." NOT IN (SELECT TOP ".$QuerySeek." ".$id['name']." FROM \\1 order by ".$id['name'].") ", $sql);
					//其他处理
					$ot1 = preg_replace('/.+(WHERE.+\)).+/i', "\\1", $sql);
					$sql = str_replace($ot1,"",$sql).' '.$ot1;
				}
				
				$query	= $sql.' '.$order;
			break;
				
			case'pgsql':
				$query	= "$sql LIMIT $limit OFFSET ".$QuerySeek;
			break;
			
			default:
				$query	= "$sql LIMIT ".$QuerySeek.",$limit";
		}
		
		$show['query']	= $this -> query($query);			//执行语句
		
		$this->log_write('Page by page query end!','#000080');
		
		$show['info']	= array(
						"limit"		=> $limit,				//每页的分页数
						"nowlimit" 	=> count($show),		//列出当前的信息数
						"nowpage"	=> ($page > $MaxPages)? $MaxPages:$page,
						"pages"		=> $MaxPages,			//分了几页
						"nums"		=> $Alls,				//当前查询中最大信息数
						);
		return $show;
	}
	
	
	/**
	*  显示数据库查询类别
	*/
	function sql_num(){
		return (int)$this -> DB -> sql_num();
	}
	
	
	/**
	*  显示详细的SQL语句
	*/
	function sql_debug(){
		return $this -> color($this -> DB -> sql_debug());
	}
	
	
	/**
	*  SQL语句色彩处理
	*/
	function color($show){
			$show = preg_replace('/(.+?)=/i', '<font color=#800000>\\1</font>=', $show);
			$show = preg_replace('/(\'|")(.+?)(\'|")/i', '<font color=#FF00FF>\\1\\2\\3</font>', $show);
			$show = preg_replace( "#\s{1,}(AND|OR|<>)\s{1,}#i", " <font color='dodgerblue'>\\1</font> "    , $show );
			$show = preg_replace( "#(FROM|INTO|UPDATE|JOIN)\s{1,}(\S+?)\s{1,}#is", "\\1<font color='#8899DF'>&nbsp;<b><u>\\2</u></b>&nbsp;</font>" , $show);
			$show = str_replace('<br>', "<br><hr size='1' width='85%' align='left' color='navyblue'>", $show);
			$show = preg_replace('#(UPDATE|SELECT)#i', '<font color="deepskyblue"><b>\\1</b></font>', $show);
			$show = preg_replace('#(DELETE)#i', '<font color="red"><b>\\1</b></font>', $show);
			$show = preg_replace('#(INSERT|VALUES|INTO)#i', '<font color="limegreen"><b>\\1</b></font>', $show);
			$show = preg_replace("#(LEFT JOIN|RIGHT JOIN)#i", '<font color="salmon"><b>\\1</b></font>', $show);
			$show = preg_replace("#(FROM|WHERE|LIMIT|SET)#i", '<font color="#18A3F5"><b>\\1</b></font>' , $show );
			$show = preg_replace("#(ORDER BY|ASC|DESC|AS)#i", '<font color="slategray"><b>\\1</b></font>', $show);
			$show = str_replace('&nbsp;&nbsp;<font>', '&nbsp;<font style="color:#808080;font-face:Arial;font-size: 11px">Date:', $show);
		return $show;
	}
	
	
	/**
	*  错误处理机制
	*/
	function error($sql='',$e_id='',$e_msg=''){
		include_once dirname(__FILE__).'/error.php';
		
		
			
			//错误类别细节分析
			switch($this->dbtype){
				case'mysql':
					$error		= mysql_errno();
					$error_msg  = (mysql_error()=='')?'N/A':mysql_error();
				break;
				case'odbc':
					$error		= odbc_error();
					$error_msg  = (odbc_errormsg()=='')?'N/A':odbc_errormsg();
				break;
				default:
					$error		= 5178;		//未知语句错误
					$error_msg  = 'There are mistakes in the current sentence, please check the spelling.';
			}
		//指定错误信息则载入系统错误信息
		if (trim($e_msg)!=''){
			$error		= $e_id;
			$error_msg	= $e_msg;
		}
		
		$this->log_write($error_msg,'red');
		new DB_Error($this->color($sql),$this->db_name,$error,$error_msg);
	}
	
	/**
	*  log日志系统
	*/
	function log_write($sql,$color=''){
		
		//缓存目录检测以及运行模式
		$chmods = @substr(@sprintf('%o', @fileperms($this->debug_file)), -3);
		
		if (in_array($chmods,array(777,644))){
			
			//文字增加颜色
			$sql = ($color)?'<font color="'.$color.'">'.$sql.'</font>':$sql;
			$microt = explode(" ",microtime());
			$sql = '$sql_log[] = array(\'type\'=>\''.$this->db_name."','time'=>'".date('Y-m-d H:i:s')."','sessionid'=>'".session_id()."','query'=>'".base64_encode($sql)."','url'=>'http://".$_SERVER['HTTP_HOST'].$_SERVER['REQUEST_URI']."');\n";
			
			//检测大小，当大于10M的时候就建立新文件
			$set_size = 20;
			
			if (number_format(filesize($this->debug_file.'/sqllog.php')/1024/1024,2)>=$set_size){
				//更名
				rename($this->debug_file.'/sqllog.php',$this->debug_file.'/_bak_'.date("YmdHis").'.php');
			}
			
			
			if (!is_file($this->debug_file.'/sqllog.php')){
				$sql = "<?".$sql;
			}
			$this->writer($this->debug_file.'/sqllog.php',$sql,'a+');
		}
		
	}
	
	/**
	*	写入函数
	*	writer(文件名,写入数据, 写入数据方式);
	*/
	function writer(
				$filename,
				$data='',
				$mode='w'
			){
		if(trim($filename)){
			$file = @fopen($filename, $mode);
			  $filedata = @fwrite($file, $data);
			@fclose($file);
		}
		if(!is_file($filename)){
			die('Sorry,'.$filename.' file write in failed!');
		}
	}
	
}
?>